package com.genghis.ptas.comment.dao.impl;

import com.genghis.core.page.dao.PageDao;
import com.genghis.core.page.entity.Page;
import com.genghis.core.page.entity.PageParameter;
import com.genghis.ptas.comment.dao.CommentDao;
import com.genghis.ptas.comment.entity.Comment;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 *
 * @author tiangc
 * @version 1.0.1
 */

@Repository("commentDao")
public class CommentDaoImpl extends PageDao implements CommentDao {
    @Override
    public int addScormComment(Comment comment) {
            String sql = "INSERT INTO scorm_comments(scorm_id, user_id, comment_content, comment_time, comment_score) VALUES(:scormId, :userId, :commentContent, :commentTime, :commentScore)";
            KeyHolder keyHolder = new GeneratedKeyHolder();
            getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(comment), keyHolder);
            return keyHolder.getKey().intValue();
    }

    @Override
    public Comment findCommentByScormIdAndUseId(int scormId, int userId) {
        String sql="SELECT * FROM scorm_comments s WHERE s.user_Id="+userId+" AND s.scorm_id="+scormId;
        return getJdbcTemplate().queryForObject(sql.toString(),new BeanPropertyRowMapper<Comment>(Comment.class));
    }

    @Override
    public Comment findCommentById(int id) {
        String sql="SELECT * FROM scorm_comments s WHERE s.id="+id;
        return getJdbcTemplate().queryForObject(sql.toString(),new BeanPropertyRowMapper<Comment>(Comment.class));
    }

    @Override
    public int findIsCommentById(int scormId, int userId) {
        String sql="SELECT COUNT(*) FROM scorm_comments s WHERE s.user_Id="+userId+" AND s.scorm_id="+scormId;
        return getJdbcTemplate().queryForInt(sql.toString());
    }

    @Override
    public List<Comment> findCommentListByScormId(int scormId) {
        String sql = "SELECT * FROM scorm_comments WHERE scorm_id='" + scormId + "'";
        return getJdbcTemplate().query(sql, new BeanPropertyRowMapper<Comment>(Comment.class));
    }

    @Override
    public int deleteCommentByid(int id) {
        String sql = "DELETE FROM scorm_comments WHERE id = ?";
        return getJdbcTemplate().update(sql, id);
    }

    @Override
    public void editComment(Comment comment) {
        String sql = "UPDATE scorm_comments SET comment_score = :commentScore,comment_content = :commentContent WHERE id = :id";
        getNamedParameterJdbcTemplate().update(sql, new BeanPropertySqlParameterSource(comment));
    }

    @Override
    public Page<Comment> listScormComment(PageParameter pageParameter, Comment comment) {
        StringBuilder sql=new StringBuilder("SELECT * FROM scorm_comments WHERE scorm_id="+comment.getScormId());
        if(comment.getCommentScore()!=0){
            sql.append(" AND comment_score ='").append(comment.getCommentScore()).append("'");
        }
        return queryForPage(pageParameter, sql.toString(), new BeanPropertySqlParameterSource(comment), new BeanPropertyRowMapper<Comment>(Comment.class));
    }
}